#1. 设置工作路径
#setwd("/Users/marikobayashi/Desktop/R/")
#path =getwd()
#2.用openxlsx包读取Excel
# a<-read.xlsx("score.xlsx",sheet=1)#文件名+sheet的序号，简单粗暴

library(openxlsx)

dat = read.xlsx("./score.xlsx",sheet=1)

#3. 生成数据框 X_Score，只取  学号 +姓名 ， 总分  
name="c("
score="c("
length_of_record=length(dat[,1])-2
for (i in 1:(length_of_record-1)) {
  name= paste(name, "'",  dat[i,1], dat[i,2], "',")
  score= paste(score, "'",  dat[i,18],"',")     #col =18 是总分栏  
}
name= paste("names_ntu =", name, "'",  dat[length_of_record,1], dat[length_of_record,2], "')")
score=paste("scores_ntu =", score, "'",  dat[length_of_record,18], "')")

# 下面将字符串转变为命令执行
class(name)
class(score)
eval(parse(text=name))
eval(parse(text=score))

# 3.1 生成数据框 ,第二列转化为数值型来处理 as.numeric()
x_Score=data.frame(names_ntu,as.numeric(scores_ntu))

# 3.2 数据框按总分排序 （降序 ） 
XX=x_Score[order(x_Score[,2],decreasing = TRUE),]

# 画 条形图 

#barplot(rev(x_Score$as.numeric.scores_ntu),horiz=T,xlim=c(0,100),axes=T,col=rep(brewer.pal(9,'YlOrRd'),each=15)) 
tp = barplot(rev(XX$as.numeric.scores_ntu),names.arg=XX$names_ntu,horiz=T,xlim=c(0,100),axes=T)  
text(tp,XX$as.numeric.scores_ntu,labels=XX$as.numeric.scores_ntu,pos = 1,srt=60,offset = 0)

text(seq(from=0.7,length.out=1,by=1.2),x=-2,label=rev(XX$names_ntu))  
axis(3,c(0,0.25,0.5,0.75,1),c('0','25','50','75','100')) 

